import pandas as pd
import pymysql
import os
import numpy as np
from datetime import datetime
import warnings

warnings.filterwarnings('ignore')


def import_excel_files():
    # 数据库连接配置
    db_config = {
        'host': 'localhost',
        'user': 'root',
        'password': 'hui123456',
        'db': 'wh',
        'charset': 'utf8mb4'
    }

    # 文件路径列表
    file_paths = [
        r"G:\工作\2025年订单\9月\抖音4.xlsx",
        r"G:\工作\2025年订单\9月\抖音1.xlsx",
        r"G:\工作\2025年订单\9月\抖音2.xlsx",
        r"G:\工作\2025年订单\9月\抖音3.xlsx"
    ]

    # 完整的列名映射
    column_mapping = {
        '平台': 'platform',
        '主订单编号': 'main_order_no',
        '子订单编号': 'sub_order_no',
        '选购商品': 'selected_product',
        '商品规格': 'product_specification',
        '商品数量': 'product_quantity',
        '商品ID': 'product_id',
        '商家编码': 'merchant_code',
        '商品单价': 'product_unit_price',
        '订单应付金额': 'order_payable_amount',
        '运费': 'shipping_fee',
        '优惠总金额': 'total_discount_amount',
        '平台优惠': 'platform_discount',
        '商家优惠': 'merchant_discount',
        '达人优惠': 'talent_discount',
        '商家改价': 'merchant_price_adjustment',
        '支付优惠': 'payment_discount',
        '红包抵扣': 'red_packet_deduction',
        '支付方式': 'payment_method',
        '手续费': 'handling_fee',
        '收件人': 'receiver_name',
        '收件人手机号': 'receiver_phone',
        '省': 'province',
        '市': 'city',
        '区': 'district',
        '街道': 'street',
        '详细地址': 'detailed_address',
        '是否修改过地址': 'address_modified',
        '买家留言': 'buyer_remark',
        '订单提交时间': 'order_submit_time',
        '旗帜颜色': 'flag_color',
        '商家备注': 'merchant_remark',
        '订单完成时间': 'order_complete_time',
        '支付完成时间': 'payment_complete_time',
        'APP渠道': 'app_channel',
        '流量来源': 'traffic_source',
        '订单状态': 'order_status',
        '承诺发货时间': 'promised_delivery_time',
        '订单类型': 'order_type',
        '鲁班落地页ID': 'luban_landing_page_id',
        '达人ID': 'talent_id',
        '达人昵称': 'talent_nickname',
        '所属门店ID': 'store_id',
        '售后状态': 'after_sales_status',
        '取消原因': 'cancel_reason',
        '预约发货时间': 'scheduled_delivery_time',
        '仓库ID': 'warehouse_id',
        '仓库名称': 'warehouse_name',
        '是否安心购': 'is_anxin_purchase',
        '广告渠道': 'ad_channel',
        '流量类型': 'traffic_type',
        '流量体裁': 'traffic_format',
        '流量渠道': 'traffic_channel',
        '发货主体': 'shipping_entity',
        '发货主体明细': 'shipping_entity_details',
        '发货时间': 'shipping_time',
        '降价类优惠': 'price_reduction_discount',
        '平台实际承担优惠金额': 'platform_actual_discount_amount',
        '商家实际承担优惠金额': 'merchant_actual_discount_amount',
        '达人实际承担优惠金额': 'talent_actual_discount_amount',
        '预计送达时间': 'estimated_delivery_time',
        '是否平台仓自流转': 'is_platform_warehouse_auto_transfer',
        '车型': 'vehicle_type',
        '商品69码': 'product_69_code',
        '发货SN码': 'shipping_sn_code',
        '发货IMEI码1': 'shipping_imei_code1',
        '发货IMEI码2': 'shipping_imei_code2',
        '是否福袋订单': 'is_lucky_bag_order',
        '福袋采购订单号': 'lucky_bag_purchase_order_no',
        '福袋采购订单状态': 'lucky_bag_purchase_order_status',
        '福袋采购订单采购方ID': 'lucky_bag_purchase_buyer_id',
        '福袋采购订单采购方名称': 'lucky_bag_purchase_buyer_name',
        '福袋采购订单创建时间': 'lucky_bag_purchase_create_time',
        '福袋采购订单支付时间': 'lucky_bag_purchase_payment_time',
        '福袋采购订单订单应付金额': 'lucky_bag_purchase_payable_amount',
        '福袋采购订单订单实际付款金额': 'lucky_bag_purchase_actual_amount',
        '福袋采购订单订单优惠金额': 'lucky_bag_purchase_discount_amount',
        '福袋采购订单商品ID': 'lucky_bag_purchase_product_id',
        '福袋采购订单商品名称': 'lucky_bag_purchase_product_name',
        '福袋采购订单商品单价': 'lucky_bag_purchase_product_price',
        '福袋采购订单商品数量': 'lucky_bag_purchase_product_quantity',
        '福袋采购订单核销数量': 'lucky_bag_purchase_write_off_quantity',
        '福袋采购订单结算数量': 'lucky_bag_purchase_settlement_quantity',
        '预约送达时间': 'scheduled_arrival_time',
        '建议发货时间（起）': 'suggested_shipping_time_start',
        '建议发货时间（止）': 'suggested_shipping_time_end',
        '物流SN码': 'logistics_sn_code',
        '物流IMEI码1': 'logistics_imei_code1',
        '物流IMEI码2': 'logistics_imei_code2'
    }

    try:
        # 连接数据库
        connection = pymysql.connect(**db_config)
        cursor = connection.cursor()

        print("开始导入Excel文件数据到douyin_order表...")
        print("=" * 60)

        total_imported = 0
        file_stats = []

        for file_path in file_paths:
            if not os.path.exists(file_path):
                print(f"文件不存在: {file_path}")
                continue

            # 获取文件名和平台标识（使用文件名如"抖音4"）
            file_name = os.path.basename(file_path)
            platform = os.path.splitext(file_name)[0]  # 如 "抖音4"

            print(f"正在处理文件: {file_name}")
            print(f"平台标识: {platform}")

            try:
                # 读取Excel文件
                df = pd.read_excel(file_path)
                original_rows = len(df)
                print(f"  文件总行数: {original_rows}")

                # 重命名列
                df = df.rename(columns=column_mapping)

                # 添加平台字段（使用文件名作为平台标识）
                df['platform'] = platform

                # 更彻底的数据清理函数
                def clean_value(value):
                    if pd.isna(value) or value == '' or str(value).lower() in ['nan', 'null', 'none', 'n/a']:
                        return None
                    return value

                # 应用通用清理到所有列
                for col in df.columns:
                    df[col] = df[col].apply(clean_value)

                # 数值型字段处理
                numeric_columns = [
                    'product_quantity', 'product_unit_price', 'order_payable_amount',
                    'shipping_fee', 'total_discount_amount', 'platform_discount',
                    'merchant_discount', 'talent_discount', 'merchant_price_adjustment',
                    'payment_discount', 'red_packet_deduction', 'handling_fee',
                    'price_reduction_discount', 'platform_actual_discount_amount',
                    'merchant_actual_discount_amount', 'talent_actual_discount_amount',
                    'lucky_bag_purchase_payable_amount', 'lucky_bag_purchase_actual_amount',
                    'lucky_bag_purchase_discount_amount', 'lucky_bag_purchase_product_price',
                    'lucky_bag_purchase_product_quantity',
                    'lucky_bag_purchase_write_off_quantity', 'lucky_bag_purchase_settlement_quantity'
                ]

                for col in numeric_columns:
                    if col in df.columns:
                        df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)

                # 布尔型字段处理
                bool_columns = [
                    'address_modified', 'is_anxin_purchase', 'is_lucky_bag_order',
                    'is_platform_warehouse_auto_transfer'
                ]

                for col in bool_columns:
                    if col in df.columns:
                        def convert_bool(x):
                            if pd.isna(x) or str(x).lower() in ['nan', 'null']:
                                return 0
                            x_str = str(x).strip().lower()
                            return 1 if x_str in ['是', 'true', '1', 'yes', 'y'] else 0

                        df[col] = df[col].apply(convert_bool)

                # 日期时间字段处理
                datetime_columns = [
                    'order_submit_time', 'order_complete_time', 'payment_complete_time',
                    'promised_delivery_time', 'scheduled_delivery_time', 'shipping_time',
                    'estimated_delivery_time', 'scheduled_arrival_time',
                    'suggested_shipping_time_start', 'suggested_shipping_time_end',
                    'lucky_bag_purchase_create_time', 'lucky_bag_purchase_payment_time'
                ]

                for col in datetime_columns:
                    if col in df.columns:
                        df[col] = pd.to_datetime(df[col], errors='coerce', utc=True)
                        df[col] = df[col].dt.tz_localize(None)  # 移除时区

                # 确保所有数据库字段存在
                for col in column_mapping.values():
                    if col not in df.columns:
                        df[col] = None

                # 最终NaN清理
                df = df.replace({np.nan: None, 'NaN': None})

                # 过滤无效行（主订单编号不能为空）
                valid_rows = df[df['main_order_no'].notna() & (df['main_order_no'] != '')].copy()
                if len(valid_rows) < original_rows:
                    print(f"  警告: 过滤无效行，主订单编号为空的 {original_rows - len(valid_rows)} 行被跳过")
                df = valid_rows
                original_rows = len(df)

                if original_rows == 0:
                    print(f"  ❌ 无有效数据，跳过文件 {file_name}")
                    continue

                # 构建插入SQL（如果表有import_time字段，可添加）
                columns = ', '.join([f"`{col}`" for col in df.columns])
                placeholders = ', '.join(['%s'] * len(df.columns))
                insert_sql = f"INSERT IGNORE INTO douyin_order ({columns}) VALUES ({placeholders}) ON DUPLICATE KEY UPDATE import_time = CURRENT_TIMESTAMP"
                # 使用 INSERT IGNORE 忽略主键冲突

                # 批量插入数据
                batch_size = 1000
                imported_rows = 0
                success_batches = 0

                for i in range(0, len(df), batch_size):
                    batch = df.iloc[i:i + batch_size]
                    values = []

                    # 逐行处理，确保没有NaN
                    for _, row in batch.iterrows():
                        cleaned_row = []
                        for val in row:
                            if pd.isna(val) or (isinstance(val, float) and np.isnan(val)):
                                cleaned_row.append(None)
                            else:
                                cleaned_row.append(val)
                        values.append(tuple(cleaned_row))

                    try:
                        cursor.executemany(insert_sql, values)
                        connection.commit()

                        imported_rows += len(values)
                        success_batches += 1
                        if success_batches % 10 == 0:  # 每10批次显示一次
                            print(f"  批次 {success_batches}: 已导入 {imported_rows}/{original_rows} 行数据")
                    except Exception as batch_error:
                        print(f"  批次 {success_batches + 1} 插入失败: {batch_error}")
                        connection.rollback()
                        # 尝试逐条插入
                        single_success = 0
                        for row_data in values:
                            try:
                                cursor.execute(insert_sql, row_data)
                                connection.commit()
                                single_success += 1
                            except Exception as single_error:
                                print(f"    单条记录插入失败: {single_error}")
                                # 不回滚单条失败，继续下一个

                        imported_rows += single_success
                        print(f"  批次 {success_batches + 1}: 成功插入 {single_success}/{len(values)} 行")

                file_stats.append({
                    'file': file_name,
                    'platform': platform,
                    'original_rows': original_rows,
                    'imported_rows': imported_rows
                })

                total_imported += imported_rows
                print(f"  ✅ 文件导入完成: {imported_rows} 行")
                print(f"  📊 平台标识: {platform}")

            except Exception as e:
                print(f"  ❌ 导入文件 {file_name} 时出错: {str(e)}")
                import traceback
                traceback.print_exc()
                connection.rollback()

        print("=" * 60)
        print("导入完成！详细统计信息：")
        for stat in file_stats:
            print(f"  文件: {stat['file']}")
            print(f"  平台: {stat['platform']}")
            print(f"  原始行数: {stat['original_rows']} 行")
            print(f"  导入行数: {stat['imported_rows']} 行")
            if stat['original_rows'] == stat['imported_rows']:
                print(f"  ✅ 全部导入成功")
            else:
                print(f"  ⚠️  有 {stat['original_rows'] - stat['imported_rows']} 行数据未导入")
            print("-" * 60)

        print(f"📈 总计导入: {total_imported} 行数据")

    except pymysql.Error as e:
        print(f"数据库连接错误: {e}")
    except Exception as e:
        print(f"程序执行错误: {e}")
        import traceback
        traceback.print_exc()
    finally:
        # 关闭连接
        if 'connection' in locals():
            try:
                cursor.close()
                connection.close()
                print("数据库连接已关闭")
            except:
                pass


# 执行导入
if __name__ == "__main__":
    import_excel_files()
